package com.bot.adms.dao.impl;

import com.bot.adms.dao.IAdSpScDetailVideoDao;
import com.bot.adms.model.entity.AdSpScDetailVideo;
import com.bot.adms.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author 席浩
 *@version 1.0
 *
 * */

public class AdSpScDetailVideoDaoImpl implements IAdSpScDetailVideoDao {


    Connection conn;
    Statement stmt;

    /**
     * 添加广告视频信息
     *
     * @param adSpScDetailVideo 广告视频信息，包含：视频文件路径，时长等等
     * @return Integer 添加的条数
     */
    @Override
    public Integer  insertAdSpScDetailVideo(AdSpScDetailVideo adSpScDetailVideo) {

        /**第一步
         * 获取数据库链接
         * */

        int i;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            /**
             * 设置sql语句
             * */
            String sql = "INSERT INTO `bo_adms`.`ad_sp_sc_detail_video` (" +
                    "`video_url`, `total_time`, `resolution`, " +
                    "`ad_sp_sc_detail_id`" +
                    ") VALUES (" +
                    "'" + adSpScDetailVideo.getVideoUrl() + "', " + // 申请名称
                    adSpScDetailVideo.getTotalTime() + ", " +          // 持续时间（数值类型无需单引号）
                    adSpScDetailVideo.getResolution() + ", " + // 视频分辨率
                    adSpScDetailVideo.getAdSpScDetailId()      // 广告分屏明细id
                    +  // 申请唯一ID（BIGINT类型无需单引号）
                    ")";
            System.out.println(sql);

            /**
             * 执行sql语句
             * */
            i = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
        System.out.println(i);
        return i;

    }



    /**
     * 修改广告视频信息
     *
     * @param adSpScDetailVideo 广告视频信息,包含：视频文件路径，时长等等
     * @return Integer 修改的条数
     */
    @Override
    public Integer updateAdSpScDetailVideo(AdSpScDetailVideo adSpScDetailVideo) {
        /**第一步
         * 获取数据库链接 w
         * */
        int i;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            /**
             * 设置sql语句
             * */
            String sql = "UPDATE `bo_adms`.`ad_sp_sc_detail_video` SET " +
                    "`video_url` = '" + adSpScDetailVideo.getVideoUrl().replace("'", "\\'") + "', " +
                    "`total_time` = " + adSpScDetailVideo.getTotalTime() + ", " +
                    "`resolution` = '" + adSpScDetailVideo.getResolution() + "', " +
                    "`ad_sp_sc_detail_id` = " + adSpScDetailVideo.getAdSpScDetailId() + " " +
                    "WHERE `id` = " + adSpScDetailVideo.getId();
            System.out.println(sql);

            /**
             * 执行sql语句
             * */
            i = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
        return i;
    }

    /**
     * 删除广告视频信息
     *
     * @param id 广告视频信息id
     * @return Integer 删除的条数
     */
    @Override
    public Integer deleteAdSpScDetailVideo(Long id) {
        /**第一步
         * 获取数据库链接 w
         * */
        int i;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            /**
             * 设置sql语句
             * */
            String sql = "DELETE FROM `bo_adms`.`ad_sp_sc_detail_video` " +
                    "WHERE `id` = " + id; // 必须通过WHERE指定删除的记录（通常用主键id）
            System.out.println(sql);

            /**
             * 执行sql语句
             * */
            i = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt);
        }
        System.out.println("yes");
        return i;
    }

    /**
     * 查找广告视频信息
     *
     * @param id 广告视频信息id
     * @return AdSpScDetailVideo 广告视频信息,包含：视频文件路径，时长等等
     */
    @Override
    public AdSpScDetailVideo getAdSpScDetailVideoById(Long id) {
        /**第一步
         * 获取数据库链接 w
         * */
        ResultSet rs = null;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            /**
             * 设置sql语句
             * */
            String sql = "SELECT * FROM `bo_adms`.`ad_sp_sc_detail_video` " +
                    "WHERE `id` = " + id;
            System.out.println(sql);

            /**
             * 执行sql语句
             * */
            rs = stmt.executeQuery(sql);
            System.out.println("执行查询SQL：" + sql);
            // 遍历查询结果集
            // 封装查询结果到视图对象
            AdSpScDetailVideo deo;
            if (rs.next()) { // 如果存在记录
                deo = new AdSpScDetailVideo();
                // 封装字段值（此时指针已在第一条记录，可安全获取）
                deo.setId(rs.getLong("id"));
                deo.setVideoUrl(rs.getString("video_url"));
                deo.setTotalTime(rs.getLong("total_time"));
                deo.setResolution(rs.getLong("resolution"));
                deo.setAdSpScDetailId(rs.getLong("ad_sp_sc_detail_id"));
                System.out.println(deo);
            } else {
                System.out.println("未找到id为" + id + "的记录");
                deo = null;
            }
            return deo;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }


    }

    /**
     * 查询广告视频信息
     *
     * @param adSpScDetailVideo 广告视频信息,包含：视频文件路径，时长等等
     * @return List<AdSpScDetailVideo> 广告视频信息集合,包含：视频文件路径，时长等等
     */
    @Override
    public List<AdSpScDetailVideo> searchAdSpScDetailVideo(AdSpScDetailVideo adSpScDetailVideo) {
        /**第一步
         * 获取数据库链接 w
         * */
        ResultSet rs = null;
        try {
            //1.获取Connection连接
            conn = JdbcUtils.getConnection();
            //2.通过连接获取statement对象
            stmt = conn.createStatement();
            /**
             * 设置sql语句
             * */
            String sql = "SELECT * FROM `bo_adms`.`ad_sp_sc_detail_video` WHERE 1=1 ";

// 动态拼接OR条件（满足任一条件即匹配）
            if (adSpScDetailVideo != null) {  // queryParam为传入的查询参数对象（需自行定义，类似示例中的applyInfoBo）
                // id精确匹配
                if (adSpScDetailVideo.getId() != null) {
                    sql += "AND `id` = " + adSpScDetailVideo.getId() + " ";
                }
                // videoUrl模糊匹配（如果需要精确匹配可改为 = ）
                if (adSpScDetailVideo.getVideoUrl() != null && !adSpScDetailVideo.getVideoUrl().isEmpty()) {
                    sql += "AND `video_url` LIKE '%" + adSpScDetailVideo.getVideoUrl() + "%' ";
                }
                // totalTime精确匹配
                if (adSpScDetailVideo.getTotalTime() != null) {
                    sql += "AND `total_time` = " + adSpScDetailVideo.getTotalTime() + " ";
                }
                // resolution精确匹配
                if (adSpScDetailVideo.getResolution() != null) {
                    sql += "AND `resolution` = " + adSpScDetailVideo.getResolution() + " ";
                }
                // adSpScDetailId精确匹配
                if (adSpScDetailVideo.getAdSpScDetailId() != null) {
                    sql += "AND `ad_sp_sc_detail_id` = " + adSpScDetailVideo.getAdSpScDetailId() + " ";
                }
            }

// 处理没有任何条件的情况（避免WHERE 1=1 OR 导致的语法错误）
            if (sql.endsWith("WHERE 1=1 ")) {
                sql = sql.replace("WHERE 1=1 ", "");  // 无查询条件时移除WHERE子句
            }
            System.out.println(sql);

            /**
             * 执行sql语句
             * */
            rs = stmt.executeQuery(sql);
            System.out.println("执行查询SQL：" + sql);
            // 遍历查询结果集
            List<AdSpScDetailVideo> adSpScDetailVideoList = new ArrayList<>();
            // 封装查询结果到视图对象
            List<AdSpScDetailVideo> deo = new ArrayList<AdSpScDetailVideo>();
            int j = 0;
            while (rs.next()) { // 如果存在记录
                AdSpScDetailVideo deoo = new AdSpScDetailVideo();
                // 封装字段值（此时指针已在第一条记录，可安全获取）
                deoo.setId(rs.getLong("id"));
                deoo.setVideoUrl(rs.getString("video_url"));
                deoo.setTotalTime(rs.getLong("total_time"));
                deoo.setResolution(rs.getLong("resolution"));
                deoo.setAdSpScDetailId(rs.getLong("ad_sp_sc_detail_id"));
                System.out.println(deoo);
                deo.add(deoo);
            }
            return deo;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
    }
}

